using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Web.Caching;

namespace MB.MyShare.DAL.SqlClient
{
   public class SqlMovieProvider : MovieProvider
   {
      /// <summary>
      /// Returns a collection with all the categories
      /// </summary>
      public override List<CategoryDetails> GetCategories()
      {
         using (SqlConnection cn = new SqlConnection(this.ConnectionString))
         {
            SqlCommand cmd = new SqlCommand("Movies_GetCategories", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cn.Open();            
            return GetCategoryCollectionFromReader(ExecuteReader(cmd));
         }
      }

      /// <summary>
      /// Deletes a category
      /// </summary>
      public override bool DeleteMovie(int ID)
      {
         using (SqlConnection cn = new SqlConnection(this.ConnectionString))
         {
            SqlCommand cmd = new SqlCommand("Movie_Delete", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
            cn.Open();
            int ret = ExecuteNonQuery(cmd);
            return (ret == 1);               
         }
      }

      /// <summary>
      /// Updates a Movie
      /// </summary>
      public override bool UpdateMovie(MovieDetails movie)
      {
         using (SqlConnection cn = new SqlConnection(this.ConnectionString))
         {
            SqlCommand cmd = new SqlCommand("Movies_UpdateCategory", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = movie.ID;
            cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = movie.title;
            cmd.Parameters.Add("@Year", SqlDbType.Int).Value = movie.year;
            cmd.Parameters.Add("@Category", SqlDbType.NVarChar).Value = movie.category;
            cmd.Parameters.Add("@Producer", SqlDbType.NVarChar).Value = movie.producer;
            cmd.Parameters.Add("@Director", SqlDbtype.NVarChar).Value = movie.director;
            cmd.Parameters.Add("@Summary", SqlDbType.NVarChar).Value = movie.summary;
            cn.Open();
            int ret = ExecuteNonQuery(cmd);
            return (ret == 1);
         }
      }

      /// <summary>
      /// Retrieves all movie
      /// </summary>
      public override List<MovieDetails> GetMovies(int Index, int Size)
      {
         using (SqlConnection cn = new SqlConnection(this.ConnectionString))
         {
            SqlCommand cmd = new SqlCommand("Movies_GetMovies", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@Index", SqlDbType.Int).Value = Index;
            cmd.Parameters.Add("@Size", SqlDbType.Int).Value = Size;
            cn.Open();
            return GetmovieCollectionFromReader(ExecuteReader(cmd), false);
         }
      }

      /// <summary>
      /// Returns the total number of movies
      /// </summary>
      public override int GetMovieCount()
      {
         using (SqlConnection cn = new SqlConnection(this.ConnectionString))
         {
            SqlCommand cmd = new SqlCommand("Movies_GetMovieCount", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cn.Open();
            return (int)ExecuteScalar(cmd);
         }
      }

      /// <summary>
      /// Retrieves all movies for the specified category
      /// </summary>
      public override List<MovieDetails> GetMovies(string category, int Index, int Size)
      {
         using (SqlConnection cn = new SqlConnection(this.ConnectionString))
         {
            SqlCommand cmd = new SqlCommand("Movies_GetMoviesByCategory", cn);
            cmd.Parameters.Add("@ID", SqlDbType.String).Value = category;
            cmd.Parameters.Add("@Index", SqlDbType.Int).Value = Index;
            cmd.Parameters.Add("@Size", SqlDbType.Int).Value = Size;
            cmd.CommandType = CommandType.StoredProcedure;
            cn.Open();
            return GetMovieCollectionFromReader(ExecuteReader(cmd), false);
         }
      }

      /// <summary>
      /// Returns the total number of movies for the specified category
      /// </summary>
      public override int GetMovieCount(int categoryID)
      {
         using (SqlConnection cn = new SqlConnection(this.ConnectionString))
         {
            SqlCommand cmd = new SqlCommand("Movies_GetMovieCountByCategory", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@category", SqlDbType.String).Value = category;
            cn.Open();
            return (int)ExecuteScalar(cmd);
         }
      }
   

      /// <summary>
      /// Retrieves the movie with the specified ID
      /// </summary>
      public override MovieDetails GetMovieByID(int ID)
      {
         using (SqlConnection cn = new SqlConnection(this.ConnectionString))
         {
            SqlCommand cmd = new SqlCommand("Movies_GetMovieByID", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;
            cn.Open();
            IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
            if (reader.Read())
               return GetMovieFromReader(reader, true);
            else
               return null;
         }
      }

      /// <summary>
      /// Retrieves the movie details for the movie with the specified ID
      /// </summary>
      public override string GetMovieDetails(int ID)
      {
         using (SqlConnection cn = new SqlConnection(this.ConnectionString))
         {
            SqlCommand cmd = new SqlCommand("Movies_GetMovieDetails", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;
            cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = title;
            cmd.Parameters.Add("@Category", SqlDbType.NVarChar).Value = category;
            cmd.Parameters.Add("@Year", SqlDbType.Int).Value = year;
            cmd.Parameters.Add("@Producer", SqlDbType.NVarChar).Value = producer;
            cmd.Parameters.Add("@Director", SqlDbType.NVarChar).Value = director;
            cmd.Parameters.Add("@Summary", SqlDbType.NText).Value = summary;
            cn.Open();
            return (string)ExecuteScalar(cmd); 
         }
      }

      /// <summary>
      /// Deletes an movie
      /// </summary>
      public override bool DeleteMovie(int ID)
      {
         using (SqlConnection cn = new SqlConnection(this.ConnectionString))
         {
            SqlCommand cmd = new SqlCommand("Movies_DeleteMovie", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;
            cn.Open();
            int ret = ExecuteNonQuery(cmd);
            return (ret == 1);
         }
      }

      /// <summary>
      /// Inserts a new movie
      /// </summary>
      public override int InsertMovie(MovieDetails movie)
      {
         using (SqlConnection cn = new SqlConnection(this.ConnectionString))
         {
            SqlCommand cmd = new SqlCommand("Movie_InsertMovie", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@Category", SqlDbType.Int).Value = movie.ID;
            cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = movie.Title;
            cmd.Parameters.Add("@Producer", SqlDbType.NVarChar).Value = movie.Producer;
            cmd.Parameters.Add("@Director", SqlDbType.NVarChar).Value = movie.Director;
            cmd.Parameters.Add("@Summary", SqlDbType.NText).Value = movie.Summary;
            cmd.Parameters.Add("@Approved", SqlDbType.Bit).Value = movie.Approved;
            cmd.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.Output;
            cn.Open();
            int ret = ExecuteNonQuery(cmd);
            return (int)cmd.Parameters["@ID"].Value;
         }
      }

      /// <summary>
      /// Approves an movie
      /// </summary>
      public override bool ApproveMovie(int ID)
      {
         using (SqlConnection cn = new SqlConnection(this.ConnectionString))
         {
            SqlCommand cmd = new SqlCommand("Movies_ApproveMovie", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;
            cn.Open();
            int ret = ExecuteNonQuery(cmd);
            return (ret == 1);
         }
      }

   }
}
